package com.lsh.dao;

import com.lsh.domain.User;
import com.lsh.domain.UserComment;
import com.lsh.domain.UserReply;
import com.sun.net.ssl.internal.www.protocol.https.HttpsURLConnectionOldImpl;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

/**
 * 文章评价Dao层类
 */
@Repository
public interface CommentDao {

    // 获取文章对应的评价列表
    @Select("<script>" +
            "SELECT c.*, i.nickname, h.URL, r2.reply_num" +
            "<if test='userId != null and userId != \"\"'>" +
            " , IF(cp.isPraise IS NULL, 0, 1) AS isPraise, c.comment_uid = #{userId} AS status" +
            "</if>" +
            " FROM user_comments c" +
            " LEFT JOIN user_headportrait h" +
            " ON c.comment_uid = h.uid" +
            " LEFT JOIN user_informations i" +
            " ON c.comment_uid = i.uid" +
            " LEFT JOIN (SELECT COUNT(comment_id) as reply_num, comment_id FROM user_replys GROUP BY comment_id) r2" +
            " ON c.id = r2.comment_id" +
            "<if test='userId != null and userId != \"\"'>" +
            " LEFT JOIN (SELECT comment_id AS id, IF(comment_id IS NULL, 0, 1) AS isPraise" +
            " FROM users_comments_praise WHERE user_id = #{userId}) cp" +
            " ON c.id = cp.id " +
            "</if>" +
            " WHERE c.artid = #{artId}" +
            " ORDER BY comment_time DESC" +
            "</script>")
    List<HashMap<String, Object>> findCommentList(@Param("artId") String artId, @Param("userId") String userId);

    // 保存用户评价
    @Insert("INSERT INTO user_comments" +
            " (artid, comment_uid, comment_name, comment_content, comment_time)" +
            " VALUES" +
            " (#{artId}, #{commentUid}, #{commentUserName}, #{commentContent}, #{commentTime});")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    int saveComment(UserComment userComment);

    // 获取评价对应的回复列表
    @Select("<script>" +
            "SELECT r.*, i1.nickname AS reply_from_nickname, i2.nickname AS reply_to_nickname, h.URL AS reply_from_headurl" +
            "<if test='userId != null and userId != \"\"'>" +
            " , IF(rp.isPraise IS NULL, 0, 1) AS isPraise, r.reply_from_uid = #{userId} AS status" +
            "</if>" +
            " FROM user_replys r" +
            " LEFT JOIN user_informations i1" +
            " ON r.reply_from_uid = i1.uid" +
            " LEFT JOIN user_informations i2" +
            " ON r.reply_to_uid = i2.uid" +
            " LEFT JOIN user_headportrait h" +
            " ON r.reply_from_uid = h.uid" +
            "<if test='userId != null and userId != \"\"'>" +
            " LEFT JOIN (SELECT reply_id AS id, IF(reply_id IS NULL, 0, 1) AS isPraise" +
            " FROM users_replys_praise WHERE user_id = #{userId}) rp" +
            " ON r.id = rp.id " +
            "</if>" +
            " WHERE r.comment_id = #{commentId}" +
            " ORDER BY reply_time DESC;" +
            "</script>")
    List<HashMap<String, Object>> findReplyList(@Param("commentId") String commentId, @Param("userId") String userId);

    // 保存用户回复
    @Insert("INSERT INTO user_replys" +
            " (comment_id, reply_content, reply_time, reply_from_uid, reply_from_name, reply_to_uid, reply_to_name)" +
            " VALUES" +
            " (#{commentId}, #{replyComment}, #{replyTime}, #{fromUid}, #{fromUsername}, #{toUid}, #{toUsername});")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    int saveReply(UserReply userReply);

    // 删除指定id的评价(包括该评价下所有回复：数据库使用了级联删除)
    @Delete("DELETE FROM user_comments WHERE comment_uid = #{userId} AND id  = #{commentId}")
    int deleteComment(@Param("userId") String usrId, @Param("commentId") String commentId);

    // 删除指定id的回复
    @Delete("DELETE FROM user_replys WHERE reply_from_uid = #{userId} and id = #{commentId}")
    int deleteReply(@Param("userId") String usrId, @Param("commentId") String commentId);

    // 保存点赞用户与评价的关联数据
    @Insert("INSERT INTO users_comments_praise (user_id, comment_id) VALUES (#{user_id}, #{comment_id})")
    boolean savePraise(@Param("user_id") String praiseUserId, @Param("comment_id") String commentId);

    // 查询点赞用户与评价的关联数据是否存在
    @Select("SELECT IFNULL((SELECT id FROM users_comments_praise WHERE user_id = #{user_id} AND comment_id = #{comment_id}), 0) as id;")
    int findPraise(@Param("user_id") String praiseUserId, @Param("comment_id") String commentId);

    // 删除点赞用户与评价的关联数据
    @Delete("DELETE FROM users_comments_praise WHERE user_id = #{user_id} AND comment_id = #{comment_id}")
    boolean deletePraise(@Param("user_id") String praiseUserId, @Param("comment_id") String commentId);

    //更新评价表中的点赞数量
    @Update("<script>" +
            "UPDATE user_comments" +
            "<set>" +
            "<if test='type==\"add\"'>" +
            " praise_num = praise_num + 1" +
            "</if>" +
            "<if test='type==\"sub\"'>" +
            " praise_num = praise_num - 1" +
            "</if>" +
            "</set>" +
            " WHERE id = #{comment_id}" +
            "</script>")
    boolean updateCommentPraiseNum(@Param("comment_id") String commentId, @Param("type") String updateType);


    // 保存点赞用户与回复的关联数据
    @Insert("INSERT INTO users_replys_praise (user_id, reply_id) VALUES (#{user_id}, #{reply_id})")
    boolean saveReplyPraise(@Param("user_id") String praiseUserId, @Param("reply_id") String ReplyId);

    // 查询点赞用户与回复的关联数据是否存在
    @Select("SELECT IFNULL((SELECT id FROM users_replys_praise WHERE user_id = #{user_id} AND reply_id = #{reply_id}), 0) as id;")
    int findReplyPraise(@Param("user_id") String praiseUserId, @Param("reply_id") String ReplyId);

    // 删除点赞用户与回复的关联数据
    @Delete("DELETE FROM users_replys_praise WHERE user_id = #{user_id} AND reply_id = #{reply_id}")
    boolean deleteReplyPraise(@Param("user_id") String praiseUserId, @Param("reply_id") String ReplyId);

    // 更新回复表中的点赞数量
    @Update("<script>" +
            "UPDATE user_replys" +
            "<set>" +
            "<if test='type==\"add\"'>" +
            " praise_num = praise_num + 1" +
            "</if>" +
            "<if test='type==\"sub\"'>" +
            " praise_num = praise_num - 1" +
            "</if>" +
            "</set>" +
            " WHERE id = #{reply_id}" +
            "</script>")
    boolean updateReplyPraiseNum(@Param("reply_id") String ReplyId, @Param("type") String updateType);

    //根据评价id获取对应文章作者用户id
    @Select("SELECT uid" +
            " FROM user_articles" +
            " WHERE id = (SELECT artid FROM user_comments WHERE id = #{commentId}) ")
    int findUserIdByCommentId(int commentId);

    // 根据id查找当前用户的所有评价记录
    @Select("SELECT c.id, c.artid, c.comment_content, FROM_UNIXTIME(c.comment_time/1000,'%Y年%m月%d日 %H:%i:%s') AS create_time, i.nickname, u.username, a.title" +
            " FROM user_comments c" +
            " LEFT JOIN user_articles a" +
            " ON c.artid = a.id" +
            " LEFT JOIN user_informations i" +
            " ON a.uid = i.uid" +
            " LEFT JOIN users u" +
            " ON a.uid = u.id" +
            " WHERE c.comment_uid = #{userId}" +
            " ORDER BY c.comment_time DESC")
    @Results(value = {
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "artid", property = "artId"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "title", property = "artTitle"),
            @Result(column = "comment_content", property = "commentContent"),
            @Result(column = "nickname", property = "toNickname"),
            @Result(column = "username", property = "toUsername")
    })
    List<LinkedHashMap<String, Object>> findCommentAllByUid(String userId);

    // 根据评价id删除对应评价
    @Delete("DELETE FROM user_comments WHERE id = #{commentId} and comment_uid = #{userId}")
    int deleteCommentById(@Param("commentId") String commentId, @Param("userId") String userId);

    // 根据id查找当前用户的所有回复记录
    @Select("SELECT r.id, r.reply_content, from_unixtime(r.reply_time/1000, '%Y年%m月%d日 %H:%i:%s') AS create_time, r.reply_to_name, i.nickname" +
            " FROM user_replys r" +
            " LEFT JOIN user_informations i" +
            " ON r.reply_to_uid = i.uid" +
            " WHERE reply_from_uid = #{userId}" +
            " ORDER BY r.reply_time DESC")
    @Results(value = {
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "reply_content", property = "replyContent"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "reply_to_name", property = "toUsername"),
            @Result(column = "nickname", property = "toNickname")
    })
    List<LinkedHashMap<String, Object>> findReplyAllByUid(String userId);

    // 根据回复id删除对应回复
    @Delete("DELETE FROM user_replys WHERE id = #{replyId} and reply_from_uid = #{userId}")
    int deleteReplyById(@Param("replyId") String replyId, @Param("userId") String userId);



}
